﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Dapper.Extensions.Common;

namespace Dapper.Extensions
{
    /// <summary>
    /// SqlMapper扩展-支持lamba表达式的查询方法
    /// </summary>
    public partial class SqlMapperExtensions
    {
        /// <summary>
        /// 查询单个对象,无数据返回NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间</param>
        /// <param name="buffered">是否缓存结果，默认为true</param>
        /// <returns>一个对象或NULL(无结果时返回)</returns>
        public static T QuerySingle<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = true) where T : class
        {

            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT top 1 * FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = "1=1";
            }
            szSql = szSql + whereClause;

            var oReturn = connection.Query<T>(szSql, null, transaction, commandTimeout: commandTimeout, buffered: buffered).FirstOrDefault();

            return oReturn;
        }

        /// <summary>
        /// 查询一个列表
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间</param>
        /// <param name="buffered">是否缓存结果，默认为true</param>
        /// <returns>一个列表或一个空列表(无结果时返回)</returns>
        public static List<T> QueryList<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = true) where T : class
        {
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT * FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;
            var list = connection.Query<T>(szSql, null, transaction, commandTimeout: commandTimeout, buffered: buffered).ToList();
            return list;
        }


        /// <summary>
        /// 按条件更新指定属性值，该方法灵活，但非强类型
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="props">更新属性值，为一个匿名对象</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static bool UpdateSpecialFields<T>(this IDbConnection connection, object props, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            bool bReturn = false;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"UPDATE {poco.Table.TableName} ";


            var dic = props.GetObjectValues();
            string setSql = SqlGenerator.GetSetSqlString(poco, dic);
            szSql = szSql + setSql;

            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            szSql = szSql + whereClause;


            int nAffectCount = connection.Execute(szSql, null, transaction, commandTimeout: commandTimeout);
            bReturn = nAffectCount > 0;


            return bReturn;

        }

        /// <summary>
        /// 按条件更新指定属性值，该方法为强类型方法
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="entityToUpdate">要更新的实体，并非是先查找出来，而是new出来的，只给想更新的列赋值,如：new User(){UserId=1}</param>
        /// <param name="predicate">谓词表达式,即更新条件</param>
        /// <param name="toUpdateProperties">要更新的列的lamda表达式,如：m=>m.UserId,此处为一个lamda表达式的params参数</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static bool UpdateSpecialFields<T>(this IDbConnection connection, T entityToUpdate, Expression<Func<T, bool>> predicate, params Expression<Func<T, object>>[] toUpdateProperties) where T : class
        {
            bool bReturn = false;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"UPDATE {poco.Table.TableName} ";


            var toUpdateColumns = SqlGenerator.GetMemberNameFromLamdaList(toUpdateProperties);
            string setSql = SqlGenerator.GetSetSqlString(poco, entityToUpdate, toUpdateColumns);
            szSql = szSql + setSql;

            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            szSql = szSql + whereClause;


            int nAffectCount = connection.Execute(szSql);
            bReturn = nAffectCount > 0;


            return bReturn;

        }

        /// <summary>
        /// 根据谓词表达式删除数据
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="predicate"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static bool Delete<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            bool bReturn = false;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"DELETE FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = "1=1";
            }
            szSql = szSql + whereClause;


            int nAffectCount = connection.Execute(szSql, null, transaction, commandTimeout: commandTimeout);
            bReturn = nAffectCount > 0;


            return bReturn;

        }


        /// <summary>
        /// 按谓词表达式取第一条数据，支持单个排序字段，无数据返回null
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="sortSingle">单个排序条件</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间(in seconds)</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static T GetTopFirstByOrder<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, Sort sortSingle = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var oReturn = default(T);
            var listSort = new List<Sort>();
            if (sortSingle != null)
            {
                listSort.Add(sortSingle);
            }
            oReturn = GetTopFirstByMultiOrder(connection, predicate, listSort, transaction, commandTimeout);
            return oReturn;

        }
        /// <summary>
        /// 按谓词表达式取第一条数据，支持多个排序字段，无数据返回null
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="listSort">单个排序条件</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static T GetTopFirstByMultiOrder<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, List<Sort> listSort = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var oReturn = default(T);

            var list = GetTopList(connection, predicate, 1, listSort, transaction, commandTimeout);
            if (list.Any())
            {
                oReturn = list[0];
            }

            return oReturn;
        }


        /// <summary>
        /// 根据谓词表达式获取前N条数据
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="topLimit">前N条数量值</param>
        /// <param name="listSort">排序列表</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令超时时间(in seconds)</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static List<T> GetTopList<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, int topLimit = 1, List<Sort> listSort = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {

            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT TOP {topLimit} * FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;

            string szOrderBy = string.Empty;
            if (listSort != null && listSort.Any())
            {

                szOrderBy = " ORDER BY ";
                listSort.ForEach(m =>
                {
                    var szDirection = m.Direction == EnumSortDirection.Ascending ? " ASC" : " DESC";
                    szOrderBy += (" " + m.PropertyName + " " + szDirection) + ",";
                });
                szOrderBy = szOrderBy.TrimEnd(',');
            }
            szSql += szOrderBy;

            var list = connection.Query<T>(szSql, null, transaction, commandTimeout: commandTimeout).ToList();

            return list;

        }



        /// <summary>
        /// 根据条件获取统计个数(int)
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">数据执行操时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static int GetCount<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var nReturn = 0;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT COUNT(1) FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;



            var oCount = connection.ExecuteScalar(szSql, null, transaction, commandTimeout: commandTimeout);
            if (oCount != null)
            {
                nReturn = Convert.ToInt32(oCount);
            }
            return nReturn;

        }

        /// <summary>
        /// 根据条件获取统计个数(long)
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">数据执行操时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static long GetLongCount<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var nReturn = 0L;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            string szSql = $"SELECT COUNT(1) FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;



            var oCount = connection.ExecuteScalar(szSql, null, transaction, commandTimeout: commandTimeout);
            if (oCount != null)
            {
                nReturn = Convert.ToInt64(oCount);
            }
            return nReturn;

        }
        /// <summary>
        /// 根据条件检查是否存在某个数据，常用在添加或更新时去重的情景中,存在返回true,否则返回false
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">数据执行操时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static bool CheckIfExist<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            bool bReturn = false;
            var nCount = GetCount(connection, predicate, transaction, commandTimeout);
            if (nCount > 0)
            {
                bReturn = true;
            }
            return bReturn;
        }

        /// <summary>
        /// 根据条件获取数据求和(sum)值
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="selector">求和值字段筛选器</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">数据执行操时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TResult">TResult只能为为int,long,float,double,decimal中的一种数据类型</typeparam>
        /// <returns></returns>
        public static TResult GetSum<T, TResult>(this IDbConnection connection, Expression<Func<T, TResult>> selector, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null)
            where T : class
            where TResult : struct
        {
            TResult willReturn = default(TResult);
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);

            var sumField = SqlGenerator.GetMemberNameFromExpression(selector);

            string szSql = $"SELECT SUM({sumField}) FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;



            var oCount = connection.ExecuteScalar(szSql, null, transaction, commandTimeout: commandTimeout);
            if (oCount != null)
            {
                var convertType = typeof(TResult);
                willReturn = (TResult)TypeConvertHelper.ChangeType(oCount, convertType);
            }
            return willReturn;

        }

        /// <summary>
        /// 根据条件获取数据平均(avg)值
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="selector">求平均值字段筛选器</param>
        /// <param name="predicate">谓词表达式</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">数据执行操时时间（in seconds）</param>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TResult">TResult只能为为int,long,float,double,decimal中的一种数据类型</typeparam>
        /// <returns></returns>
        public static TResult GetAvg<T, TResult>(this IDbConnection connection, Expression<Func<T, TResult>> selector, Expression<Func<T, bool>> predicate, IDbTransaction transaction = null, int? commandTimeout = null)
            where T : class
            where TResult : struct
        {
            TResult willReturn = default(TResult);
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);

            var sumField = SqlGenerator.GetMemberNameFromExpression(selector);

            string szSql = $"SELECT AVG({sumField}) FROM {poco.Table.TableName} WHERE ";
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1=1 ";
            }
            szSql = szSql + whereClause;



            var oCount = connection.ExecuteScalar(szSql, null, transaction, commandTimeout: commandTimeout);
            if (oCount != null)
            {
                var convertType = typeof(TResult);
                willReturn = (TResult)TypeConvertHelper.ChangeType(oCount, convertType);
            }
            return willReturn;

        }



        /// <summary>
        /// 批量插入方法，使用SqlBulkCopy方法
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="list">插入数据列表</param>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static bool InsertBatch<T>(this IDbConnection connection, IEnumerable<T> list) where T : class
        {
            bool bReturn = true;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);
            try
            {
                SqlGenerator.BulkCopy(connection, poco, list);
            }
            catch (Exception ex)
            {
                bReturn = false;
            }

            return bReturn;
        }



        /// <summary>
        ///  根据条件表达式获取分页数据，单字段排序（传【表达式和排序方向】方式）
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">条件表达式</param>

        /// <param name="sortFieldExpression"></param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令执行时间（in seconds）</param>
        /// <param name="direction"></param>
        public static Page<T> GetPageList<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, Expression<Func<T, object>> sortFieldExpression, EnumSortDirection direction, long pageIndex = 1, long pageSize = 10, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            var listSort = new List<Sort>();
            if (sortFieldExpression != null)
            {
                listSort.Add(Sort.CreateSort(sortFieldExpression, direction));
            }
            else
            {
                throw new Exception("sortFieldExpression 为必传参数");
            }
            return GetPageList(connection, predicate, listSort, pageIndex, pageSize, transaction, commandTimeout);
        }
        /// <summary>
        ///  根据条件表达式获取分页数据，多字段排序
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="predicate">条件表达式</param>
        /// <param name="listSort">排序字段列表</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">命令执行时间（in seconds）</param>

        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static Page<T> GetPageList<T>(this IDbConnection connection, Expression<Func<T, bool>> predicate, List<Sort> listSort, long pageIndex = 1, long pageSize = 10, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            var pageList = new Page<T>()
            {
                rows = new List<T>(),
                total = 0
            };

            var translator = new QueryTranslator();
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);
            var adapter = GetFormatter(connection);
            string whereClause = translator.Translate<T>(predicate);
            if (string.IsNullOrEmpty(whereClause))
            {
                whereClause = " 1= 1 ";
            }

            string szPageSql = adapter.GetPagedSql<T>(connection, poco.Table.TableName, whereClause, pageIndex, pageSize,
                listSort).ToString();
            var gridReader = connection.QueryMultiple(szPageSql, null, transaction, commandTimeout: commandTimeout);

            var itemList = gridReader.Read<T>();
            var totalCount = gridReader.ReadFirst<int>();
            pageList = new Page<T>
            {
                rows = itemList.ToList(),
                total = totalCount
            };

            return pageList;
        }

        /// <summary>
        /// 根据lamda表达式获取一个指定字段的值
        /// </summary>
        /// <param name="connection">数据连接</param>
        /// <param name="queryField">要查询的字段的lamda的表达式，如：m.ProductName</param>
        /// <param name="predicate">条件Lamda表达式m.Product==30</param>
        /// <param name="useNoLock">是否使用with(nolock)表限定词，可选，默认使用</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <typeparam name="T">要搜索的表对应的实体类</typeparam>
        /// <typeparam name="TResult">返回的数据类型，如：string,int,logn,decimal等</typeparam>
        /// <returns></returns>
        public static TResult QureySingleField<T,TResult>(this IDbConnection connection, Expression<Func<T, object>> queryField, Expression<Func<T, bool>> predicate,bool useNoLock=true, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            bool bReturn = false;
            Type modelType = typeof(T);

            PocoData poco = PocoDatasCache(modelType);

            var translator = new QueryTranslator();
            string whereClause = translator.Translate<T>(predicate);
            var filedName = SqlGenerator.GetMemberNameFromPredicate(queryField);

            var szNolock = useNoLock ? " with(nolock) " : "";
            string szSql = $"SELECT {filedName} FROM {poco.Table.TableName} {szNolock}";
            
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            szSql = szSql + whereClause;


            return connection.ExecuteScalar<TResult>(szSql, null, transaction, commandTimeout: commandTimeout);
        }
    }
}